關於外鍵限制的探討(二)

DAY 29

MySQL那些事兒系列第 29 篇

mysql database 資料庫鐵人賽

一級屠豬士

2013-10-29 10:33:26

10102 瀏覽

分享至

接續昨天的外鍵限制探討.
昨天泰大有提到,可以先把Child的資料刪掉,
再刪Parent的資料.
除此之外,還有其他作法.
刪掉原本的限制,新增限制.

先查看昨天建立的限制名稱.

SHOW CREATE TABLE	Billings\G
*************************** 1. row ***************************
       Table: Billings
Create Table: CREATE TABLE `Billings` (
  `galid` int(10) unsigned NOT NULL,
  `bill_date` date NOT NULL,
  `bill_rate` int(10) unsigned NOT NULL,
  PRIMARY KEY (`galid`,`bill_date`),
  CONSTRAINT `Billings_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

是 Billings_ibfk_1

ALTER TABLE Billings
 DROP FOREIGN KEY Billings_ibfk_1
    ,
  ADD CONSTRAINT FOREIGN KEY (galid) REFERENCES Girls (galid) 
   ON UPDATE CASCADE
   ON DELETE CASCADE;

再來修改 Worklog

SHOW CREATE TABLE Worklog\G
*************************** 1. row ***************************
       Table: Worklog
Create Table: CREATE TABLE `Worklog` (
  `galid` int(10) unsigned NOT NULL,
  `work_date` date NOT NULL,
  `work_hour` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`galid`,`work_date`),
  CONSTRAINT `Worklog_ibfk_1` FOREIGN KEY (`galid`) REFERENCES `Girls` (`galid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

是 Worklog_ibfk_1

ALTER TABLE Worklog
 DROP FOREIGN KEY Worklog_ibfk_1
    ,
  ADD CONSTRAINT FOREIGN KEY (galid) REFERENCES Girls (galid) 
   ON UPDATE CASCADE
   ON DELETE CASCADE;

這樣就都修改了.

現在修改 Girls的資料,將初音的編號由1號改為5號.

UPDATE Girls
   SET galid = 5
 WHERE galid = 1;

查看3個Table 現在資料的情況.

SELECT *
  FROM Girls;

+-------+--------------------+
| galid | name               |
+-------+--------------------+
|     2 | 桜木凛             |
|     3 | 希崎ジェシカ       |
|     4 | 葵つかさ           |
|     5 | 初音みのり         |
+-------+--------------------+

SELECT *
  FROM Billings;

+-------+------------+-----------+
| galid | bill_date  | bill_rate |
+-------+------------+-----------+
|     2 | 2013-01-01 |        26 |
|     2 | 2013-07-01 |        32 |
|     3 | 2013-01-01 |        24 |
|     4 | 2013-01-01 |        27 |
|     4 | 2013-08-01 |        35 |
|     5 | 2013-01-01 |        25 |
|     5 | 2013-07-01 |        30 |
+-------+------------+-----------+

SELECT *
  FROM Worklog;

+-------+------------+-----------+
| galid | work_date  | work_hour |
+-------+------------+-----------+
|     2 | 2013-02-02 |         4 |
|     2 | 2013-08-02 |         6 |
|     3 | 2013-03-01 |         6 |
|     3 | 2013-08-03 |         8 |
|     4 | 2013-06-01 |         3 |
|     4 | 2013-09-01 |         5 |
|     5 | 2013-02-01 |         5 |
|     5 | 2013-08-01 |         7 |
+-------+------------+-----------+

原本1號的資料都更新為5號了.

TedGalRptVIEW的資料,也跟著變化.

SELECT *
     , work_hour * bill_rate AS '費用'
  FROM TedGalRpt;

+-------+--------------------+------------+-----------+-----------+--------+
| galid | name               | work_date  | work_hour | bill_rate | 費用   |
+-------+--------------------+------------+-----------+-----------+--------+
|     2 | 桜木凛             | 2013-02-02 |         4 |        26 |    104 |
|     2 | 桜木凛             | 2013-08-02 |         6 |        32 |    192 |
|     3 | 希崎ジェシカ       | 2013-03-01 |         6 |        24 |    144 |
|     3 | 希崎ジェシカ       | 2013-08-03 |         8 |        24 |    192 |
|     4 | 葵つかさ           | 2013-06-01 |         3 |        27 |     81 |
|     4 | 葵つかさ           | 2013-09-01 |         5 |        35 |    175 |
|     5 | 初音みのり         | 2013-02-01 |         5 |        25 |    125 |
|     5 | 初音みのり         | 2013-08-01 |         7 |        30 |    210 |
+-------+--------------------+------------+-----------+-----------+--------+
8 rows in set (0.00 sec)

接著試試看刪除.初音現在是5號了.要刪除5號.

DELETE 
  FROM Girls
 WHERE galid = 5;

查看三個Table的資料.

SELECT *
  FROM Girls;

+-------+--------------------+
| galid | name               |
+-------+--------------------+
|     2 | 桜木凛             |
|     3 | 希崎ジェシカ       |
|     4 | 葵つかさ           |
+-------+--------------------+

SELECT *
  FROM Billings;

+-------+------------+-----------+
| galid | bill_date  | bill_rate |
+-------+------------+-----------+
|     2 | 2013-01-01 |        26 |
|     2 | 2013-07-01 |        32 |
|     3 | 2013-01-01 |        24 |
|     4 | 2013-01-01 |        27 |
|     4 | 2013-08-01 |        35 |
+-------+------------+-----------+

SELECT *
  FROM Worklog;

+-------+------------+-----------+
| galid | work_date  | work_hour |
+-------+------------+-----------+
|     2 | 2013-02-02 |         4 |
|     2 | 2013-08-02 |         6 |
|     3 | 2013-03-01 |         6 |
|     3 | 2013-08-03 |         8 |
|     4 | 2013-06-01 |         3 |
|     4 | 2013-09-01 |         5 |
+-------+------------+-----------+


SELECT *
     , work_hour * bill_rate AS '費用'
  FROM TedGalRpt;

+-------+--------------------+------------+-----------+-----------+--------+
| galid | name               | work_date  | work_hour | bill_rate | 費用   |
+-------+--------------------+------------+-----------+-----------+--------+
|     2 | 桜木凛             | 2013-02-02 |         4 |        26 |    104 |
|     2 | 桜木凛             | 2013-08-02 |         6 |        32 |    192 |
|     3 | 希崎ジェシカ       | 2013-03-01 |         6 |        24 |    144 |
|     3 | 希崎ジェシカ       | 2013-08-03 |         8 |        24 |    192 |
|     4 | 葵つかさ           | 2013-06-01 |         3 |        27 |     81 |
|     4 | 葵つかさ           | 2013-09-01 |         5 |        35 |    175 |
+-------+--------------------+------------+-----------+-----------+--------+

初音的相關資料全部被刪除了,而我們只是把Parent的初音刪掉,MySQL就會自動全刪掉了.
跟昨天的行為完全不同.